package com.sparrow.orm;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.sparrow.constant.CACHE_KEY;
import com.sparrow.constant.COMMON;
import com.sparrow.core.Cache;
import com.sparrow.core.Log;
import com.sparrow.enums.ORDER;
import com.sparrow.enums.ORM_ENTITY_META_DATA;
import com.sparrow.utils.StringUtil;

public class SQLHelper {
	private StringBuffer fields = new StringBuffer();
	private List<String> setClause = new ArrayList<String>();
	private List<String> whereClause = new ArrayList<String>();
	private List<String> orderClause = new ArrayList<String>();
	private List<SQLParameter> updateParameters = new ArrayList<SQLParameter>();
	private List<SQLParameter> whereParameters = new ArrayList<SQLParameter>();
	private int pageSize = COMMON.ALL;
	private Object currentPageIndex = 1;
	private int recordCount = 0;
	private Map<String, Class<?>> entityMap = new HashMap<String, Class<?>>();
	private Map<String, Map<String, DBField>> dbFieldMap = new HashMap<String, Map<String, DBField>>();
	private boolean isDistinct = false;
	private int tableBucketKeyId = -1;
	private Dialect dialect;

	public Dialect getDialect() {
		return dialect;
	}

	public void setDialect(Dialect dialect) {
		this.dialect = dialect;
	}

	public int getTableBucketKeyId() {
		return tableBucketKeyId;
	}

	public void setTableBucketKeyId(int tableBucketKeyId) {
		this.tableBucketKeyId = tableBucketKeyId;
	}

	public boolean isDistinct() {
		return isDistinct;
	}

	public void setDistinct(boolean isDistinct) {
		this.isDistinct = isDistinct;
	}

	public SQLHelper() {
	}

	public SQLHelper(Class<?>... args) {
		// 表的第一个对象即可获取方言
		String dialectKey = args[0].getSimpleName() + "."
				+ ORM_ENTITY_META_DATA.DIALECT.ordinal();
		this.dialect = Dialect.getInstance(Cache.getInstance()
				.get(CACHE_KEY.ORM, dialectKey).toString());

		for (int i = 0; i < args.length; i++) {
			String clazzName = args[i].getSimpleName();
			String dbFiledKey = clazzName + "."
					+ ORM_ENTITY_META_DATA.DBFIELDS.ordinal();
			this.entityMap.put(clazzName, args[i]);
			@SuppressWarnings("unchecked")
			List<DBField> dbFieldList = (List<DBField>) Cache.getInstance()
					.get(CACHE_KEY.ORM, dbFiledKey);
			Map<String, DBField> dbFieldMapPerEntity = new LinkedHashMap<String, DBField>();
			for (int j = 0; j < dbFieldList.size(); j++) {
				dbFieldMapPerEntity.put(dbFieldList.get(j).FieldName()
						.toLowerCase(), dbFieldList.get(j));
			}
			this.dbFieldMap.put(clazzName.toLowerCase(), dbFieldMapPerEntity);
		}
	}

	public DBField getDBField(String propertyName) {
		System.out.println(propertyName);
		String[] property = propertyName.trim().split("\\.");
		try {
			return this.dbFieldMap.get(property[0].toLowerCase()).get(
					property[1].toLowerCase());
		} catch (SecurityException e) {
			System.out.println(propertyName);
			Log.getInstance().error(e);
		}
		return null;
	}

	public String getSafeLikeContent(String value) {
		if (StringUtil.isNullOrEmpty(value)) {
			value = "";
		} else {
			// mysql 转义字符
			value = value.replace("'", "\\'");
			value = value.replace("%", "\\%").replace("_", "\\_");
		}
		return value;
	}

	public String getLimitClause() {
		int pageIndex = this.getCurrentPageIndex() == 0 ? 0 : this
				.getCurrentPageIndex() - 1;
		int size = Integer.valueOf(pageSize);
		return " limit " + (pageIndex * size) + "," + pageSize;
	}

	public String getMoreUnderLine(int length) {
		char[] underLines = new char[length];
		for (int i = 0; i < length; i++) {
			underLines[i] = '_';
		}
		return String.valueOf(underLines);
	}

	/**
	 * @author zlz
	 * 
	 *         table_alias.fieldName,...
	 * @time 2013-4-18上午10:41:45
	 * @param fields
	 * @return
	 */
	public void setFields(String fields) {
		if (!StringUtil.isNullOrEmpty(this.fields)) {
			this.fields = new StringBuffer();
		}
		if (!StringUtil.isNullOrEmpty(fields)) {
			String[] dbfields = fields.split("\\,");
			for (int i = 0; i < dbfields.length; i++) {
				this.fields.append(dbfields[i].split("\\.")[0] + "."
						+ this.getDBField(dbfields[i]).FieldName() + ",");
			}
		}
	}

	public void setFieldsWithoutAlias(String fields) {
		if (!StringUtil.isNullOrEmpty(fields)) {
			this.fields = new StringBuffer();
		}
		if (!StringUtil.isNullOrEmpty(fields)) {
			this.fields.append(fields);
		}
	}

	/**
	 * 用于无参的条件子句
	 * 
	 * @author zhanglizhi
	 * @date 2013-4-21
	 * @version 1.0
	 * @param whereClause
	 * @return
	 */
	public SQLHelper setWhereClause(String whereClause) {
		this.whereClause.add(whereClause);
		return this;
	}

	private String getAlias(String propertyName) {
		return propertyName.split("\\.")[0].toLowerCase() + ".";
	}

	private String relationOperation(String propertyName, String operation,
			Object value, boolean withAlias) {
		return this.relationOperation(propertyName, operation, value,
				withAlias, false);
	}

	private String relationOperation(String propertyName, String operation,
			Object value, boolean withAlias, boolean isUpdate) {
		DBField dbField = this.getDBField(propertyName);

		String alies = withAlias ? this.getAlias(propertyName) : "";
		if (value == null) {
			return alies + dbField.FieldName() + " is null";
		} else {
			if (isUpdate) {
				this.updateParameters.add(new SQLParameter(dbField.FieldType(),
						dbField.FieldName(), value));
			} else {
				this.whereParameters.add(new SQLParameter(dbField.FieldType(),
						dbField.FieldName(), value));

			}
			return alies + dbField.FieldName() + operation + "?";
		}
	}

	public SQLHelper equal(String propertyName, Object value) {
		this.whereClause.add(this.relationOperation(propertyName, "=", value,
				true));
		return this;
	}

	public SQLHelper greatEqual(String propertyName, Object value) {
		this.whereClause.add(this.relationOperation(propertyName, ">=", value,
				true));
		return this;
	}

	public SQLHelper greatThen(String propertyName, Object value) {
		this.whereClause.add(this.relationOperation(propertyName, ">", value,
				true));
		return this;
	}

	public SQLHelper lessEqual(String propertyName, Object value) {
		this.whereClause.add(this.relationOperation(propertyName, "<=", value,
				true));
		return this;
	}

	public SQLHelper lessThen(String propertyName, Object value) {
		this.whereClause.add(this.relationOperation(propertyName, "<", value,
				true));
		return this;
	}

	public SQLHelper noEqual(String propertyName, Object value) {
		this.whereClause.add(this.relationOperation(propertyName, "!=", value,
				true));
		return this;
	}

	/**
	 * 通配符和是否过滤通配符由调用者传入
	 * 
	 * @author zlz
	 * 
	 * @time 2013-4-18上午11:55:15
	 * @param propertyName
	 * @param value
	 * @return
	 */
	public SQLHelper like(String propertyName, Object value) {
		this.whereClause.add(this.relationOperation(propertyName, " like",
				value, true));
		return this;
	}

	public SQLHelper in(String propertyName, String value) {
		this.whereClause.add(this.getAlias(propertyName)
				+ this.getDBField(propertyName).FieldName() + " in(" + value
				+ ")");
		return this;
	}

	public SQLHelper isDBnull(String propertyName) {
		this.whereClause.add(" isnull(" + this.getAlias(propertyName)
				+ this.getDBField(propertyName).FieldName() + ")");
		return this;
	}

	public SQLHelper isNotDBnull(String propertyName) {
		this.whereClause.add(" !isnull(" + this.getAlias(propertyName)
				+ this.getDBField(propertyName).FieldName() + ")");
		return this;
	}

	public SQLHelper equalWithoutAlias(String propertyName, Object value) {
		this.whereClause.add(this.relationOperation(propertyName, "=", value,
				false));
		return this;
	}

	public SQLHelper greatEqualWithoutAlias(String propertyName, Object value) {
		this.whereClause.add(this.relationOperation(propertyName, ">=", value,
				true));
		return this;
	}

	public SQLHelper greatThenWithoutAlias(String propertyName, Object value) {
		this.whereClause.add(this.relationOperation(propertyName, ">", value,
				false));
		return this;
	}

	public SQLHelper lessEqualWithoutAlias(String propertyName, Object value) {
		this.whereClause.add(this.relationOperation(propertyName, "<=", value,
				false));
		return this;
	}

	public SQLHelper lessThenWithoutAlias(String propertyName, Object value) {
		this.whereClause.add(this.relationOperation(propertyName, "<", value,
				false));
		return this;
	}

	public SQLHelper noEqualWithoutAlias(String propertyName, Object value) {
		this.whereClause.add(this.relationOperation(propertyName, "!=", value,
				false));
		return this;
	}

	/**
	 * 通配符和是否过滤通配符由调用者传入
	 * 
	 * @author zlz
	 * 
	 * @time 2013-4-18上午11:55:15
	 * @param propertyName
	 * @param value
	 * @return
	 */
	public SQLHelper likeWithoutAlias(String propertyName, Object value) {
		this.whereClause.add(this.relationOperation(propertyName, "like",
				value, false));
		return this;
	}

	public SQLHelper inWithoutAlias(String propertyName, String value) {
		this.whereClause.add(this.getDBField(propertyName).FieldName() + " in("
				+ value + ")");
		return this;
	}

	public SQLHelper isDBnullWithoutAlias(String propertyName) {
		this.whereClause.add(" isnull("
				+ this.getDBField(propertyName).FieldName() + ")");
		return this;
	}

	public SQLHelper isNotDBnullWithoutAlias(String propertyName) {
		this.whereClause.add(" !isnull("
				+ this.getDBField(propertyName).FieldName() + ")");
		return this;
	}

	/**
	 * 如果set子句中有?参数，则set子句优先于where子句之前构造
	 * 
	 * @author zlz
	 * 
	 * @time 2013-4-18上午11:35:58
	 * @param whereClause
	 * @return
	 */

	public SQLHelper addOrderClause(String propertyName, ORDER order) {
		DBField dbField = this.getDBField(propertyName);
		String orderClause = this.getAlias(propertyName) + dbField.FieldName()
				+ " " + order.toString().toUpperCase();
		this.orderClause.add(orderClause);
		return this;
	}

	public SQLHelper addOrderClauseWithoutAlias(String propertyName, ORDER order) {
		DBField dbField = this.getDBField(propertyName);
		String orderClause = dbField.FieldName() + " "
				+ order.toString().toUpperCase();
		this.orderClause.add(orderClause);
		return this;
	}

	public SQLHelper addSetClause(String propertyName, Object value) {
		this.setClause.add(this.relationOperation(propertyName, "=", value,
				false, true));
		return this;
	}

	public SQLHelper addSetClauseWithPlus(String propertyName, Object value) {
		DBField dbField = this.getDBField(propertyName);
		this.updateParameters.add(new SQLParameter(dbField.FieldType(), dbField
				.FieldName(), value));
		this.setClause.add(dbField.FieldName() + "=" + dbField.FieldName()
				+ "+?");
		return this;
	}

	public SQLHelper addSetClauseWithoutParameter(String propertyName,
			String value) {
		DBField dbField = this.getDBField(propertyName);
		this.setClause.add(dbField.FieldName() + "=" + value);
		return this;
	}

	/**
	 * 在SQL已经拼好的情况的使用
	 * 
	 * 存储过程或多表关联的复杂查询
	 * 
	 * @author zhanglizhi
	 * @date 2013-4-27
	 * @version 1.0
	 * @param clazz
	 * @param value
	 * @return
	 */
	public SQLHelper addParameterWithoutSQL(Class<?> clazz,
			Object value) {
		this.whereParameters.add(new SQLParameter(clazz, "store procedure", value));
		return this;
	}

	public SQLHelper and() {
		this.whereClause.add(" and ");
		return this;
	}

	public SQLHelper or() {
		this.whereClause.add(" or ");
		return this;
	}

	public SQLHelper leftParenthesis() {
		this.whereClause.add("(");
		return this;
	}

	public SQLHelper rightParenthesis() {
		this.whereClause.add(")");
		return this;
	}

	public String getFields() {
		if (!StringUtil.isNullOrEmpty(this.fields)
				&& this.fields.indexOf(",") >= 0) {
			return this.fields.substring(0, this.fields.lastIndexOf(","));
		}
		return fields.toString();
	}

	public String getOrderClause() {
		StringBuffer orderClause = new StringBuffer();
		for (int i = 0; i < this.orderClause.size(); i++) {
			orderClause.append(this.orderClause.get(i));
			orderClause.append(",");
		}
		if (this.orderClause.size() == 0) {
			return COMMON.EMPTY;
		} else {
			return orderClause.substring(0, orderClause.lastIndexOf(","));
		}
	}

	public String getSetClause() {
		StringBuffer setClause = new StringBuffer();
		for (int i = 0; i < this.setClause.size(); i++) {
			setClause.append(this.setClause.get(i));
			setClause.append(",");
		}
		if (this.setClause.size() == 0) {
			return COMMON.EMPTY;
		} else {
			return setClause.substring(0, setClause.lastIndexOf(","));
		}
	}

	/**
	 * 如果set子句中有?参数，则set子句优先于where子句之前构造
	 * 
	 * @author zlz
	 * @time 2013-4-18上午11:35:26
	 * @return
	 */
	public String getWhereClause() {
		StringBuffer whereClause = new StringBuffer();
		for (int i = 0; i < this.whereClause.size(); i++) {
			whereClause.append(this.whereClause.get(i));
		}
		String where = whereClause.toString().trim();
		if (where.startsWith("and")) {
			return where.substring(3);
		}
		return where;
	}

	public int getPageSize() {
		return pageSize;
	}

	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}

	public int getCurrentPageIndex() {
		if (StringUtil.isNullOrEmpty(this.currentPageIndex)) {
			return 1;
		} else {
			return Integer.valueOf(this.currentPageIndex.toString());
		}
	}

	public void setCurrentPageIndex(Object currentPageIndex) {
		this.currentPageIndex = currentPageIndex;
	}

	public int getRecordCount() {
		return recordCount;
	}

	public void setRecordCount(int recordCount) {
		this.recordCount = recordCount;
	}

	public SQLParameter[] getParameters() {
		SQLParameter[] parameters = new SQLParameter[this.updateParameters
				.size() + this.whereParameters.size()];
		int i = 0;
		for (SQLParameter sqlParameter : this.updateParameters) {
			parameters[i++] = sqlParameter;
		}
		for (SQLParameter sqlParameter : this.whereParameters) {
			parameters[i++] = sqlParameter;
		}
		return parameters;
	}
}
